跳到主要内容

MySQL 遇到死锁了怎么办

死锁的例子​

当多个事务同时竞争数据库资源时，可能会发生死锁（Deadlock）。死锁是指两个或多个事务相互等待对方释放资源而无法继续执行的情况，导致系统无法进展。

下面是一个简单的MySQL死锁例子：

假设有两个表 A 和 B，每个表包含一个行字段（id）：

-- 事务 T1
START TRANSACTION;
SELECT * FROM A WHERE id = 1 FOR UPDATE;
SELECT * FROM B WHERE id = 1 FOR UPDATE;

-- 事务 T2
START TRANSACTION;
SELECT * FROM B WHERE id = 1 FOR UPDATE;
SELECT * FROM A WHERE id = 1 FOR UPDATE;

在这个例子中，事务 T1 和事务 T2 同时执行，它们尝试以相反的顺序获取对表 A 和表 B 中行 id = 1 的行的写锁。这种顺序竞争可能导致死锁。

如果事务 T1 先获取表 A 的写锁，然后事务 T2 尝试获取表 B 的写锁，而表 B 中的行 id = 1 正在被事务 T1 持有，那么事务 T2 将等待事务 T1 释放锁。同时，事务 T1 尝试获取表 B 的写锁，但该锁被事务 T2 持有，因此事务 T1 也会等待事务 T2 释放锁。这样，事务 T1 和事务 T2 互相等待对方释放锁，形成死锁。

当MySQL检测到死锁时，会选择其中一个事务作为牺牲者，回滚该事务，解除死锁状态，让其他事务继续执行。

有 MVCC 了为什么还要加锁呢？​

确实，在 MySQL 中使用 MVCC（多版本并发控制）来实现读写并发。MVCC 通过在每个数据行上保存多个版本，以实现并发读取而不会阻塞写入操作。

然而，尽管 MVCC 可以提供并发性，但在某些情况下，仍然需要使用锁来确保数据的一致性和避免并发冲突。这是因为 MVCC 只能解决读取数据（快照读）的并发问题，而在写入数据时（当前读）仍然需要处理并发冲突。

快照读是如何避免幻读的？​

可重复读隔离级是由 MVCC（多版本并发控制）实现的，实现的方式是开始事务后（执行 begin 语句后），在执行第一个查询语句后，会创建一个 Read View，后续的查询语句利用这个 Read View，通过这个 Read View 就可以在 undo log 版本链找到事务开始时的数据，所以事务过程中每次查询的数据都是一样的，即使中途有其他事务插入了新纪录，是查询不出来这条数据的，所以就很好了避免幻读问题。

做个实验，数据库表 t_stu 如下，其中 id 为主键。

然后在可重复读隔离级别下，有两个事务的执行顺序如下：

从这个实验结果可以看到，即使事务 B 中途插入了一条记录，事务 A 前后两次查询的结果集都是一样的，并没有出现所谓的幻读现象。

当前读下如何解决幻读的？​

MySQL 里除了普通查询是快照读，其他都是当前读，比如 update、insert、delete，这些语句执行前都会查询最新版本的数据，然后再做进一步的操作。

这很好理解，假设你要 update 一个记录，另一个事务已经 delete 这条记录并且提交事务了，这样不是会产生冲突吗，所以 update 的时候肯定要知道最新的数据。

另外，select ... for update 这种查询语句是当前读，每次执行的时候都是读取最新的数据。

接下来，我们假设select ... for update当前读是不会加锁的（实际上是会加锁的），在做一遍实验。

这时候，事务 B 插入的记录，就会被事务 A 的第二条查询语句查询到（因为是当前读），这样就会出现前后两次查询的结果集合不一样，这就出现了幻读。

所以，Innodb 引擎为了解决「可重复读」隔离级别使用「当前读」而造成的幻读问题，就引出了间隙锁。

假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。

举个具体例子，场景如下：

事务 A 执行了这面这条锁定读语句后，就在对表中的记录加上 id 范围为 (2, +∞] 的 next-key lock（next-key lock 是间隙锁+记录锁的组合）。

然后，事务 B 在执行插入语句的时候，判断到插入的位置被事务 A 加了 next-key lock，于是事物 B 会生成一个插入意向锁，同时进入等待状态，直到事务 A 提交了事务。这就避免了由于事务 B 插入新记录而导致事务 A 发生幻读的现象。

避免死锁的方法包括​

  • 尽量保持事务的简短和快速，减少持有锁的时间。
  • 在事务中按照固定的顺序访问数据库资源，避免不同的事务以不同的顺序获取锁。
  • 使用合理的索引和优化查询，减少锁的竞争。
  • 调整事务隔离级别，降低事务并发程度。
  • 监控和检测死锁，及时处理和解决。

通过合理的设计和调整，可以减少死锁的发生频率，并确保数据库系统的稳定性和性能。